package com.iplant.aps.serviceimpl.dao.wms;

import com.iplant.aps.service.mesenum.MESDBSource;
import com.iplant.aps.service.mesenum.MESException;
import com.iplant.aps.service.mesenum.WMSPickDemandItemStatus;
import com.iplant.aps.service.mesenum.WMSPickDemandItemStatusNew;
import com.iplant.aps.service.po.OutResult;
import com.iplant.aps.service.po.ServiceResult;
import com.iplant.aps.service.po.bms.BMSEmployee;
import com.iplant.aps.service.po.wms.WMSBatchPickDemandItem;
import com.iplant.aps.service.utils.StringUtils;
import com.iplant.aps.serviceimpl.dao.BaseDAO;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import java.text.MessageFormat;
import java.util.*;

public class WMSBatchPickDemandItemDAO extends BaseDAO {

    private static final Logger logger = LoggerFactory.getLogger(WMSBatchPickDemandItemDAO.class);

    private static WMSBatchPickDemandItemDAO Instance = null;

    private WMSBatchPickDemandItemDAO() {
        super();
    }

    public static WMSBatchPickDemandItemDAO getInstance() {
        if (Instance == null)
            Instance = new WMSBatchPickDemandItemDAO();
        return Instance;
    }

    /**
     * 添加或修改
     */
    public int Update(BMSEmployee wLoginUser, WMSBatchPickDemandItem wWMSBatchPickDemandItem,
                      OutResult<Integer> wErrorCode) {
        int wResult = 0;
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wWMSBatchPickDemandItem == null)
                return 0;

            String wSQL = "";
            if (wWMSBatchPickDemandItem.getID() <= 0) {
                wSQL = MessageFormat.format("INSERT INTO {0}.wms_batchpickdemanditem(DemandID,MaterialID,MaterialNo," +
                                "MaterialName,FQTY,RowNo,GroupFlag,AssessmentType,KittingFlag,Status,BatchNumber," +
                                "UnitText,BOMNumber,Stock,Supplier,ProductName,SerialNo,ProductTime,PickedNumber," +
                                "IsConfirmed,SorterID,Sorter,SorterNo,StockPosition,ToHandFQTY,IsOriDis) VALUES(:DemandID,:MaterialID," +
                                ":MaterialNo," +
                                ":MaterialName,:FQTY,:RowNo," +
                                ":GroupFlag,:AssessmentType,:KittingFlag,:Status,:BatchNumber,:UnitText,:BOMNumber," +
                                ":Stock,:Supplier,:ProductName,:SerialNo,:ProductTime,:PickedNumber,:IsConfirmed," +
                                ":SorterID,:Sorter,:SorterNo,:StockPosition,:ToHandFQTY,:IsOriDis);",
                        wInstance.Result);
            } else {
                wSQL = MessageFormat.format("UPDATE {0}.wms_batchpickdemanditem SET DemandID = :DemandID,MaterialID =" +
                                " :MaterialID,MaterialNo = :MaterialNo,MaterialName = :MaterialName,FQTY = :FQTY," +
                                "RowNo" +
                                " = :RowNo,GroupFlag = :GroupFlag,AssessmentType = :AssessmentType,KittingFlag = " +
                                ":KittingFlag,Status = :Status,BatchNumber = :BatchNumber,UnitText = :UnitText," +
                                "BOMNumber = :BOMNumber,Stock = :Stock,Supplier = :Supplier,ProductName = " +
                                ":ProductName,SerialNo = :SerialNo,ProductTime = :ProductTime,PickedNumber = " +
                                ":PickedNumber,IsConfirmed = :IsConfirmed,SorterID=:SorterID,Sorter=:Sorter," +
                                "SorterNo=:SorterNo,StockPosition=:StockPosition,ToHandFQTY=:ToHandFQTY,IsOriDis=:IsOriDis WHERE ID = :ID;",
                        wInstance.Result);
            }

            wSQL = this.DMLChange(wSQL);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("ID", wWMSBatchPickDemandItem.ID);
            wParamMap.put("DemandID", wWMSBatchPickDemandItem.DemandID);
            wParamMap.put("MaterialID", wWMSBatchPickDemandItem.MaterialID);
            wParamMap.put("MaterialNo", wWMSBatchPickDemandItem.MaterialNo);
            wParamMap.put("MaterialName", wWMSBatchPickDemandItem.MaterialName);
            wParamMap.put("FQTY", wWMSBatchPickDemandItem.FQTY);
            wParamMap.put("RowNo", wWMSBatchPickDemandItem.RowNo);
            wParamMap.put("GroupFlag", wWMSBatchPickDemandItem.GroupFlag);
            wParamMap.put("AssessmentType", wWMSBatchPickDemandItem.AssessmentType);
            wParamMap.put("KittingFlag", wWMSBatchPickDemandItem.KittingFlag);
            wParamMap.put("Status", wWMSBatchPickDemandItem.Status);
            wParamMap.put("BatchNumber", wWMSBatchPickDemandItem.BatchNumber);
            wParamMap.put("UnitText", wWMSBatchPickDemandItem.UnitText);
            wParamMap.put("BOMNumber", wWMSBatchPickDemandItem.BOMNumber);
            wParamMap.put("Stock", wWMSBatchPickDemandItem.Stock);
            wParamMap.put("Supplier", wWMSBatchPickDemandItem.Supplier);
            wParamMap.put("ProductName", wWMSBatchPickDemandItem.ProductName);
            wParamMap.put("SerialNo", wWMSBatchPickDemandItem.SerialNo);
            wParamMap.put("ProductTime", wWMSBatchPickDemandItem.ProductTime);
            wParamMap.put("PickedNumber", wWMSBatchPickDemandItem.PickedNumber);
            wParamMap.put("IsConfirmed", wWMSBatchPickDemandItem.IsConfirmed);
            wParamMap.put("SorterID", wWMSBatchPickDemandItem.SorterID);
            wParamMap.put("Sorter", wWMSBatchPickDemandItem.Sorter);
            wParamMap.put("SorterNo", wWMSBatchPickDemandItem.SorterNo);
            wParamMap.put("StockPosition", wWMSBatchPickDemandItem.StockPosition);
            wParamMap.put("ToHandFQTY", wWMSBatchPickDemandItem.ToHandFQTY);
            wParamMap.put("IsOriDis", wWMSBatchPickDemandItem.IsOriDis);

            KeyHolder keyHolder = new GeneratedKeyHolder();
            SqlParameterSource wSqlParameterSource = new MapSqlParameterSource(wParamMap);

            shrisJdbcTemplate.update(wSQL, wSqlParameterSource, keyHolder);

            if (wWMSBatchPickDemandItem.getID() <= 0) {
                wResult = Objects.requireNonNull(keyHolder.getKey()).intValue();
                wWMSBatchPickDemandItem.setID(wResult);
            } else {
                wResult = wWMSBatchPickDemandItem.getID();
            }
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 删除集合
     */
    public ServiceResult<Integer> DeleteList(BMSEmployee wLoginUser, List<WMSBatchPickDemandItem> wList,
                                             OutResult<Integer> wErrorCode) {
        ServiceResult<Integer> wResult = new ServiceResult<>(0);
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            if (wList == null || wList.size() <= 0)
                return wResult;

            List<String> wIDList = new ArrayList<>();
            for (WMSBatchPickDemandItem wItem : wList) {
                wIDList.add(String.valueOf(wItem.ID));
            }
            String wSql = MessageFormat.format("delete from {1}.wms_batchpickdemanditem WHERE ID IN({0}) ;",
                    String.join(",", wIDList), wInstance.Result);
            this.ExecuteSqlTransaction(wSql);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResult;
    }

    /**
     * 查单条
     */
    public WMSBatchPickDemandItem SelectByID(BMSEmployee wLoginUser, int wID, OutResult<Integer> wErrorCode) {
        WMSBatchPickDemandItem wResult = new WMSBatchPickDemandItem();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResult;
            }

            List<WMSBatchPickDemandItem> wList = SelectList(wLoginUser, wID, -1, "", -1, "", "", "", "", -1,
                    wErrorCode);
            if (wList == null || wList.size() != 1)
                return wResult;
            wResult = wList.get(0);
        } catch (Exception e) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(e.toString());
        }
        return wResult;
    }

    /**
     * 条件查询集合
     */
    public List<WMSBatchPickDemandItem> SelectList(BMSEmployee wLoginUser, int wID, int wDemandID, String wRowNo,
                                                   int wStatus, String wBatchNumber, String wStock, String wSupplier,
                                                   String wSerialNo, int wIsConfirmed, OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemandItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.DemandNo FROM {0}.wms_batchpickdemanditem t "
                            + " left join {0}.wms_batchpickdemand t1 on t.DemandID=t1.ID "
                            + "WHERE  1=1  and ( :wID <= 0 or :wID = t.ID ) and ( " +
                            ":wDemandID <= 0 or :wDemandID = t.DemandID ) and ( :wRowNo is null or :wRowNo = '''' or " +
                            ":wRowNo = t.RowNo ) and ( :wStatus <= 0 or :wStatus = t.Status ) and ( :wBatchNumber is " +
                            "null" +
                            " " +
                            "or :wBatchNumber = '''' or :wBatchNumber = t.BatchNumber ) and ( :wStock is null or " +
                            ":wStock" +
                            " = '''' or :wStock = t.Stock ) and ( :wSupplier is null or :wSupplier = '''' or " +
                            ":wSupplier" +
                            " " +
                            "= t.Supplier ) and ( :wSerialNo is null or :wSerialNo = '''' or :wSerialNo = t.SerialNo " +
                            ") " +
                            "and" +
                            " ( :wIsConfirmed <= 0 or :wIsConfirmed = t.IsConfirmed );",
                    wInstance.Result);

            Map<String, Object> wParamMap = new HashMap<>();

            wParamMap.put("wID", wID);
            wParamMap.put("wDemandID", wDemandID);
            wParamMap.put("wRowNo", wRowNo);
            wParamMap.put("wStatus", wStatus);
            wParamMap.put("wBatchNumber", wBatchNumber);
            wParamMap.put("wStock", wStock);
            wParamMap.put("wSupplier", wSupplier);
            wParamMap.put("wSerialNo", wSerialNo);
            wParamMap.put("wIsConfirmed", wIsConfirmed);

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }

    /**
     * 赋值
     */
    private void SetValue(List<WMSBatchPickDemandItem> wResultList, List<Map<String, Object>> wQueryResult) {
        try {
            for (Map<String, Object> wReader : wQueryResult) {
                WMSBatchPickDemandItem wItem = new WMSBatchPickDemandItem();

                wItem.ID = StringUtils.parseInt(wReader.get("ID"));
                wItem.DemandID = StringUtils.parseInt(wReader.get("DemandID"));
                wItem.DemandNo = StringUtils.parseString(wReader.get("DemandNo"));
                wItem.MaterialID = StringUtils.parseInt(wReader.get("MaterialID"));
                wItem.MaterialNo = StringUtils.parseString(wReader.get("MaterialNo"));
                wItem.MaterialName = StringUtils.parseString(wReader.get("MaterialName"));
                wItem.FQTY = StringUtils.parseDouble(wReader.get("FQTY"));
                wItem.RowNo = StringUtils.parseString(wReader.get("RowNo"));
                wItem.GroupFlag = StringUtils.parseString(wReader.get("GroupFlag"));
                wItem.AssessmentType = StringUtils.parseString(wReader.get("AssessmentType"));
                wItem.KittingFlag = StringUtils.parseString(wReader.get("KittingFlag"));
                wItem.Status = StringUtils.parseInt(wReader.get("Status"));
                wItem.StatusText = WMSPickDemandItemStatusNew.getEnumType(wItem.Status).getLable();
                wItem.BatchNumber = StringUtils.parseString(wReader.get("BatchNumber"));
                wItem.UnitText = StringUtils.parseString(wReader.get("UnitText"));
                wItem.BOMNumber = StringUtils.parseDouble(wReader.get("BOMNumber"));
                wItem.Stock = StringUtils.parseString(wReader.get("Stock"));
                wItem.Supplier = StringUtils.parseString(wReader.get("Supplier"));
                wItem.ProductName = StringUtils.parseString(wReader.get("ProductName"));
                wItem.SerialNo = StringUtils.parseString(wReader.get("SerialNo"));
                wItem.ProductTime = StringUtils.parseCalendar(wReader.get("ProductTime"));
                wItem.PickedNumber = StringUtils.parseDouble(wReader.get("PickedNumber"));
                wItem.IsConfirmed = StringUtils.parseInt(wReader.get("IsConfirmed"));

                wItem.SorterID = StringUtils.parseInt(wReader.get("SorterID"));
                wItem.Sorter = StringUtils.parseString(wReader.get("Sorter"));
                wItem.SorterNo = StringUtils.parseString(wReader.get("SorterNo"));
                wItem.StockPosition = StringUtils.parseString(wReader.get("StockPosition"));
                wItem.ToHandFQTY = StringUtils.parseDouble(wReader.get("ToHandFQTY"));
                wItem.IsOriDis = StringUtils.parseString(wReader.get("IsOriDis"));

                wResultList.add(wItem);
            }
        } catch (Exception ex) {
            logger.error(ex.toString());
        }
    }

    /**
     * 通过DemandIDList查询数据
     */
    public List<WMSBatchPickDemandItem> SelectListByDemandIDList(BMSEmployee wLoginUser, List<Integer> wIDList,
                                                                 OutResult<Integer> wErrorCode) {
        List<WMSBatchPickDemandItem> wResultList = new ArrayList<>();
        try {
            ServiceResult<String> wInstance = this.GetDataBaseName(wLoginUser.getCompanyID(), MESDBSource.Basic,
                    wLoginUser.getID(), 0);
            wErrorCode.set(wInstance.ErrorCode);
            if (wErrorCode.Result != 0) {
                return wResultList;
            }

            if (wIDList == null || wIDList.size() <= 0) {
                return wResultList;
            }

            String wSQL = MessageFormat.format(
                    "SELECT t.*,t1.DemandNo FROM {0}.wms_batchpickdemanditem t "
                            + " left join {0}.wms_batchpickdemand t1 on t.DemandID=t1.ID "
                            + "WHERE  1=1   " +
                            "and ( t.DemandID in ({1}) ) ;",
                    wInstance.Result, StringUtils.Join(",", wIDList));

            Map<String, Object> wParamMap = new HashMap<>();

            wSQL = this.DMLChange(wSQL);

            List<Map<String, Object>> wQueryResult = shrisJdbcTemplate.queryForList(wSQL, wParamMap);

            SetValue(wResultList, wQueryResult);
        } catch (Exception ex) {
            wErrorCode.set(MESException.DBSQL.getValue());
            logger.error(ex.toString());
        }
        return wResultList;
    }
}
